/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package persistencia;

import dto.Alumno;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.*;


/**
 *
 * @author Hades
 */
public class AlumnoDAO {

    public String bd = "alumnos1";
    public String login = "root";
    public String password = "root";
    public String url = "jdbc:mysql://localhost/" + bd;
public String control;
    public void updateAlumnoById(Alumno alumno) {
        try {
            
            Connection conn = ConnectionFactory.getConnection();
            
            PreparedStatement pstmt = null;
            PreparedStatement pstmt1 = null;
            
           
            String query = "select control from alumno where control=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, alumno.getControl());
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            String control=rs.getString("control");
          
            String query1 = "update alumno set nombre=?,apellidoPaterno=?,apellidoMaterno=?,edad=?,semestre=?,idCarrera=?,direccion=? where control=?";
            pstmt1 = conn.prepareStatement(query1);
           
            pstmt1.setString(1, alumno.getNombre());
            pstmt1.setString(2, alumno.getApellidoPaterno());
            pstmt1.setString(3, alumno.getApellidoMaterno());
            pstmt1.setInt(4, alumno.getEdad());
            pstmt1.setInt(5, alumno.getSemestre());
            pstmt1.setInt(6, alumno.getIdCarrera());
            pstmt1.setString(7, alumno.getDireccion());
            pstmt1.setString(8,control);
            pstmt1.executeUpdate();
            
//           Statement st = conn.createStatement();
//           ResultSet rs = st.executeQuery("SELECT control FROM alumno WHERE control='"+alumno.getControl()+"';");
//           rs.next();
//           String control1 = rs.getString("control");
//        
//                   
//           //String query = "update alumno set nombre='" + alumno.getNombre() + "'," + "apellidoPaterno='" + alumno.getApellidoPaterno() + "'," + "apellidoMaterno='" + alumno.getApellidoMaterno() + "'," + "edad=" + alumno.getEdad() + ",semestre=" + alumno.getSemestre() + "," + "idCarrera=" + alumno.getIdCarrera() + "," + "direccion=" + "\"" + alumno.getDireccion() + "\"" + "where control='07090146'";
//           st.executeUpdate("update alumno set nombre='" + alumno.getNombre() + "'," + "apellidoPaterno='" + alumno.getApellidoPaterno() + "'," + "apellidoMaterno='" + alumno.getApellidoMaterno() + "'," + "edad=" + alumno.getEdad() + ",semestre=" + alumno.getSemestre() + "," + "idCarrera=" + alumno.getIdCarrera() + "," + "direccion=" + "\"" + alumno.getDireccion() + "\"" + "where control='"+control1+"'");
//        

//            String query1 = "update alumno set nombre='" + alumno.getNombre() + "'," + "apellidoPaterno='" + alumno.getApellidoPaterno() + "'," + "apellidoMaterno='" + alumno.getApellidoMaterno() + "'," + "edad=" + alumno.getEdad() + ",semestre=" + alumno.getSemestre() + "," + "idCarrera=" + alumno.getIdCarrera() + "," + "direccion=" + "\"" + alumno.getDireccion() + "\"" + "where control='" + alumno.getControl() + "'";
//            Statement st = conexion.createStatement();
//            //ejecutamos el query 
//            st.executeUpdate(query1);
//            //Siempre hay que cerrar la conexion
//            st.close();
            
            

           
        } catch (SQLException ex) {
            System.out.println(ex + "Hubo un problema al intentar conectarse"
                    + "con la base de datos" + url);

        }




    }

    public void deleteAlumnoById(Alumno alumno) {
        try {
            Connection conn = ConnectionFactory.getConnection();
            
             PreparedStatement pstmt = null;

            String query = "select control from alumno where control=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, alumno.getControl());
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            String control=rs.getString("control");
          
            String query1 = "delete from alumno where control=?";
            pstmt = conn.prepareStatement(query1);
            pstmt.setString(1, control);
            pstmt.executeUpdate();
            
            
            
           


        } catch (SQLException ex) {
            System.out.println(ex + "Hubo un problema al intentar conectarse"
                    + "con la base de datos" + url);

        }



    }

    public void insertarAlumno(Alumno alumno) {
        try {
            Connection conn = ConnectionFactory.getConnection();

           
            PreparedStatement pstmt = null;

            String query = "insert into alumno values(?,?,?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, alumno.getControl());
            pstmt.setString(2, alumno.getNombre());
            pstmt.setString(3, alumno.getApellidoPaterno());
            pstmt.setString(4, alumno.getApellidoMaterno());
            pstmt.setInt(5, alumno.getEdad());
            pstmt.setInt(6, alumno.getSemestre());
            pstmt.setInt(7, alumno.getIdCarrera());
            pstmt.setString(8, alumno.getDireccion());
            pstmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println(ex + "Hubo un problema al intentar conectarse"
                    + "con la base de datos" + url);

        }

    }
        public void  ConsultarAlumno(Alumno alumno){
            try {
                
             Connection conexion=null;
            // registro de drive
            try {
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e) {
               Logger.getLogger(AlumnoDAO.class.getName()).log(Level.SEVERE, null, e);
             //JOptionPane.showMessageDialog(null,"error al cargar ell driver"+e.getMessage());
            }
            	
	     conexion=DriverManager.getConnection(url, login, password);
            //paso 1 Construimos el query     
            String query="select*from alumno where control='"+alumno.getControl()+"'";
            PreparedStatement st= conexion.prepareStatement(query);
            //ejecutamos el query 
	    ResultSet rs=st.executeQuery();
            while(rs.next())
            {
                
                alumno.setControl(rs.getString("control"));
                alumno.setNombre(rs.getString("nombre"));
                alumno.setApellidoPaterno(rs.getString("apellidoPaterno"));
                alumno.setApellidoMaterno(rs.getString("apellidoMaterno"));
                alumno.setEdad(rs.getInt("edad"));
                alumno.setSemestre(rs.getInt("semestre"));
                alumno.setIdCarrera(rs.getInt("idCarrera"));
                alumno.setDireccion(rs.getString("direccion"));
                
            }
	    //Siempre hay que cerrar la conexion
	    st.close();
            rs.close();
            conexion.close();
       
		          
            
        } catch(SQLException ex){
        System.out.println(ex+"Hubo un problema al intentar conectarse");
        

    } 
            
            
        }
        

}
